﻿Imports System.IO

Public Class dsCauThu
    'Khoi tao chi co Ma va Ten
    Dim path As String = Directory.GetCurrentDirectory
    Dim hinh As New LayHinh
    Dim ctx As New QLDBDataContext

    Private _maCT As Integer
    Private _tenCT As String
    Private _avata As Image
    Private _vitri As String
    Private _tendoi As String
    Private _quoctich As String
    Private _soao As Integer

    Sub New()
        _maCT = -1
        _soao = -1
        _tenCT = ""
        _avata = Nothing
        _vitri = ""
        _tendoi = ""
        _quoctich = ""
    End Sub

    Public Property MaCT() As Integer
        Get
            Return _maCT
        End Get
        Set(value As Integer)
            _maCT = value
        End Set
    End Property

    Public Property TenCT As String
        Get
            Return _tenCT
        End Get
        Set(value As String)
            _tenCT = value
        End Set
    End Property

    Public Property Avata As Image
        Get
            Return _avata
        End Get
        Set(value As Image)
            _avata = value
        End Set
    End Property

    Public Property ViTri As String
        Get
            Return _vitri
        End Get
        Set(value As String)
            _vitri = value
        End Set
    End Property

    Public Property TenDoi As String
        Get
            Return _tendoi
        End Get
        Set(value As String)
            _tendoi = value
        End Set
    End Property

    Public Property QuocTich As String
        Get
            Return _quoctich
        End Get
        Set(value As String)
            _quoctich = value
        End Set
    End Property

    Public Property SoAo As Integer
        Get
            Return _soao
        End Get
        Set(value As Integer)
            _soao = value
        End Set
    End Property

    Private Function LayTenDoi(ByVal _MaDoi) As String
        If _MaDoi Is Nothing Then
            Return "Cầu Thủ Tự Do"
        End If
        Dim md As Integer
        md = _MaDoi
        Dim v = From q In ctx.CAULACBOs.Where(Function(p) p.MaCLB = md)

        If Not v Is Nothing Then
            Dim clb As List(Of CAULACBO)
            clb = v.ToList()
            Return clb.Min.TenCLB
  
        Else
            Return "Cầu Thủ Tự Do"
        End If

    End Function

    Private Function LayQuocTich(ByVal _MaQT) As String
        If _MaQT Is Nothing Then
            Return "Chưa Cập Nhật Quốc Tịch"
        End If
        Dim mqt As Integer
        mqt = _MaQT
        Dim qt = From q In ctx.QUOCTICHes.Where(Function(p) p.MaQT = mqt)

        If Not qt Is Nothing Then
            Dim _QT As List(Of QUOCTICH)
            _QT = qt.ToList()
            Return _QT.Min.TenQT
        Else
            Return "Chưa Cập Nhật Quốc Tịch"
        End If
    End Function

    Public Function LayTenCT(ByVal ma As Integer) As String
        Dim query = (From p In ctx.CAUTHUs
                    Where p.MaCT = ma
                    Select p.HoTen).First()
        Return query
    End Function
    Public Function LayDSCauThu() As IQueryable(Of dsCauThu)

        Dim ds = From q In ctx.CAUTHUs
                 Order By q.MaCT Ascending
                 Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                      .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                      .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
        Return ds
    End Function

    Public Function BuilSearchQuery(ByVal TenCT As String, ByVal ViTri As String, ByVal QG As Integer, ByVal CLB As Integer) As IQueryable(Of dsCauThu)

        If TenCT = "" And QG <> -1 And CLB <> -1 And ViTri = "ALL" Then 'tìm theo tất cả các vị trí tại 1 qg và 1 cau lb
            Dim ds = From q In ctx.CAUTHUs Where q.MaCLB = CLB And q.MaQT = QG
                Order By q.MaCT Ascending
                Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                     .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                     .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds
            'ok

        ElseIf TenCT = "" And QG <> -1 And CLB <> -1 And ViTri <> "ALL" Then 'tìm theo vị trí tại một quốc tịch, câu lạc bộ
            Dim ds = From q In ctx.CAUTHUs Where q.ViTri Like ViTri And q.MaCLB = CLB And q.MaQT = QG
                Order By q.MaCT Ascending
                Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                     .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                     .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds
            'ok

        ElseIf TenCT = "" And QG = -1 And CLB = -1 And ViTri <> "ALL" Then 'tìm theo vị trí của tất cả các qg và các câu lạc bộ
            Dim ds = From q In ctx.CAUTHUs Where q.ViTri Like ViTri
                Order By q.MaCT Ascending
                Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                     .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                     .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds
            'ok
        ElseIf TenCT = "" And QG = -1 And CLB <> -1 And ViTri = "ALL" Then 'tìm theo tất cả các vị trí và tất cả các quốc gia tại 1  clb
            Dim ds = From q In ctx.CAUTHUs Where q.MaCLB = CLB
                Order By q.MaCT Ascending
                Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                     .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                     .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds
            'ok
        ElseIf TenCT = "" And QG = -1 And CLB <> -1 And ViTri <> "ALL" Then 'tìm theo vị trí của một câu lạc Bộ
            Dim ds = From q In ctx.CAUTHUs Where q.MaCLB = CLB And q.ViTri Like ViTri
                Order By q.MaCT Ascending
                Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                     .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                     .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds
            'ok
        ElseIf TenCT = "" And QG <> -1 And CLB = -1 And ViTri <> "ALL" Then 'tìm theo vị trí của một Quốc Tịch
            Dim ds = From q In ctx.CAUTHUs Where q.MaQT = QG And q.ViTri Like ViTri
                Order By q.MaCT Ascending
                Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                     .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                     .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds
            'ok
        ElseIf TenCT = "" And QG <> -1 And CLB = -1 And ViTri = "ALL" Then 'tìm theo tất cả các Câu Lạc Bộ ở một quốc gia
            Dim ds = From q In ctx.CAUTHUs Where q.MaQT = QG
                Order By q.MaCT Ascending
                Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                     .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                     .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds

            'ok



        ElseIf TenCT <> "" And QG <> -1 And CLB <> -1 And ViTri = "ALL" Then 'tìm theo tên tất cả các vị trí
            Dim ds = From q In ctx.CAUTHUs Where q.HoTen.Contains(TenCT) And q.MaCLB = CLB And q.MaQT = QG
                Order By q.MaCT Ascending
                Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                     .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                     .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds
            'ok

        ElseIf TenCT <> "" And QG <> -1 And CLB <> -1 And ViTri <> "ALL" Then 'tìm theo tên vị trí cụ thể
            Dim ds = From q In ctx.CAUTHUs Where q.HoTen.Contains(TenCT) And q.ViTri Like ViTri And q.MaCLB = CLB And q.MaQT = QG
                Order By q.MaCT Ascending
                Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                     .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                     .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds

            'ok
        ElseIf TenCT <> "" And QG <> -1 And CLB = -1 And ViTri <> "ALL" Then 'tìm theo tên vị trí cụ thể
            Dim ds = From q In ctx.CAUTHUs Where q.HoTen.Contains(TenCT) And q.ViTri Like ViTri And q.MaQT = QG
                Order By q.MaCT Ascending
                Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                     .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                     .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds

        ElseIf TenCT <> "" And QG = -1 And CLB <> -1 And ViTri = "ALL" Then 'tìm theo tên tất cả các vị trí của đội 
            Dim ds = From q In ctx.CAUTHUs Where q.HoTen.Contains(TenCT) And q.MaCLB = CLB
                Order By q.MaCT Ascending
                Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                     .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                     .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds
            'ok

        ElseIf TenCT <> "" And QG <> -1 And CLB = -1 And ViTri = "ALL" Then 'tìm theo tên tất cả các vị trí quốc tịch
            Dim ds = From q In ctx.CAUTHUs Where q.HoTen.Contains(TenCT) And q.MaQT = QG
                Order By q.MaCT Ascending
                Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                     .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                     .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds

        ElseIf TenCT <> "" And QG = -1 And CLB = -1 And ViTri = "ALL" Then 'tìm theo tên tất cả các vị trí, quốc tịch 
            Dim ds = From q In ctx.CAUTHUs Where q.HoTen.Contains(TenCT)
                Order By q.MaCT Ascending
                Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                     .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                     .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds

        ElseIf TenCT <> "" And QG = -1 And CLB = -1 And ViTri <> "ALL" Then 'tìm theo tên tất cả các vị trí, quốc tịch 
            Dim ds = From q In ctx.CAUTHUs Where q.HoTen.Contains(TenCT) And q.ViTri Like ViTri
                Order By q.MaCT Ascending
                Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                     .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                     .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds

        Else
            Dim ds = From q In ctx.CAUTHUs Where q.HoTen.Contains(TenCT) Or q.MaQT = QG Or q.MaCLB = CLB Or q.ViTri Like ViTri
              Order By q.MaCT Ascending
              Select New dsCauThu With {.MaCT = q.MaCT, ._soao = q.SoAo, .TenCT = q.HoTen,
                                   .Avata = hinh.ResizImg(path + "\images\cauthu\" + q.HINH.Trim(), 45, 30),
                                   .ViTri = q.ViTri, .TenDoi = LayTenDoi(q.MaCLB), .QuocTich = LayQuocTich(q.MaQT)}
            Return ds
        End If
    End Function

    Function LayMaCLB(ByVal TENCLB As String) As Integer
        Dim v = (From q In ctx.CAULACBOs Where q.TenCLB Like TENCLB Select q.MaCLB).First
        Return v
    End Function

    Public Function socauthu_CLB(ByVal caulacbo As String) As Integer
        Dim query = (From c In ctx.CAUTHUs
                    Where c.MaCLB = LayMaCLB(caulacbo)).Count
        Return query
    End Function

End Class
